<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class M_student_grades extends MY_Model {
	
	private $__table = 'student_grades';
	protected $_table = 'student_grades';
	protected $_timestamp = false;
	function generate_student_grades($enrollment_id, $level_id = '')
	{
		$CI =& get_instance();
		$CI->load->model('M_student_subjects');
		$CI->load->model('M_gperiods');
		$student_subjects = $CI->M_student_subjects->all_by_enrollment_id($enrollment_id);
		$grading_periods = $CI->M_gperiods->get_gperiods();
	
		if(!empty($student_subjects))
		{
			foreach($student_subjects as  $id => $r):
				foreach($grading_periods as $key=>$row):
					$data[$id.'-'.$key]['subject_id'] = $r->subject_id;
					$data[$id.'-'.$key]['enrollment_id'] = $enrollment_id;
					$data[$id.'-'.$key]['grading_period_id'] = $row->gp_id;
					$data[$id.'-'.$key]['ay_id'] = $this->c_sy->id;
				endforeach;
			endforeach;

			$this->db->where('enrollment_id',$enrollment_id)
					 ->where('ay_id',$this->c_sy->id)
					 ->delete($this->__table);
			$this->db->insert_batch($this->__table, $data);
			
			return $this->db->affected_rows() >=1 ? TRUE : FALSE;
		}
	}
	
	/**
	 * Generate Student Grade by Subject
	 * @param int $e_id
	 * @param int $subj_id
	 * @param bool 
	 */
	public function generate_grade_by_subject($e_id, $subj_id)
	{
		$this->load->model('M_gperiods');
		$grading_periods = $this->M_gperiods->get_gperiods();
		
		if($grading_periods){
			$ctr = 0;
			foreach ($grading_periods as $k => $p) {
				unset($data);
				$data['subject_id'] = $subj_id;
				$data['enrollment_id'] = $e_id;
				$data['grading_period_id'] = $p->gp_id;
				$data['ay_id'] = $this->c_sy->id;
				$rs = (object)$this->insert($data);
				if($rs->status){ $ctr++; }
			}
			return $ctr>0?TRUE:FALSE;
		}

		return false;
	}
	
	function grouped_subject_by_enrollment_id($enrollment_id)
	{
	  $sql = "	SELECT subjects.subj_code,subjects.subj_id
				FROM student_grades
				LEFT JOIN subjects ON subjects.subj_id = student_grades.subject_id
				LEFT JOIN grading_periods ON grading_periods.gp_id = student_grades.grading_period_id
				WHERE student_grades.enrollment_id = ?
				GROUP BY student_grades.subject_id";
	  
	  $query = $this->db->query($sql, $enrollment_id);
	  return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	function get_all_by_enrollment_id($enrollment_id)
	{
	  
	  $sql = "SELECT subject_id, grading_period_id, value, remarks,id
            FROM student_grades
            WHERE student_grades.enrollment_id = ?";
	  
	  $query = $this->db->query($sql, $enrollment_id);
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	function get_students_per_subject($subj_id)
	{
		$sql = "SELECT 
					enrollments.e_id,
					profiles.profile_id,
					profiles.lastname, 
					profiles.firstname, 
					profiles.middlename
				FROM profiles
				JOIN enrollments ON profiles.profile_id = enrollments.e_profile_id
				JOIN student_subjects ON enrollments.e_id = student_subjects.enrollment_id
				JOIN subjects ON student_subjects.subject_id = subjects.subj_id
				WHERE subjects.subj_id =  ?";
		$query = $this->db->query($sql, array($subj_id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	/*
		Gets all student entries that are already in the student_grades 
		table regardless if it is empty or not.
	*/
	function get_current_entries($subject_id)
	{
		$sql = "SELECT * FROM student_grades WHERE subject_id = ?";
		$query = $this->db->query($sql, array($subject_id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	function get_grades_per_student($enrollment_id, $subject_id)
	{
		$grade_arr = null;
		$sql = "SELECT * FROM grading_periods WHERE gp_stat = 'active'";
		$query = $this->db->query($sql);
		$count = $query->num_rows();
		for($i = 0; $i < $count; $i++)
		{
			$g_sql = "SELECT value, remarks, letter_grade FROM student_grades
						WHERE grading_period_id = ?
						AND enrollment_id = ?
						AND subject_id = ?";
			$g_query = $this->db->query($g_sql, array($i + 1, $enrollment_id, $subject_id));
			if($g_query->num_rows() > 0)
			{
				$row = $g_query->row();
				$grade_arr[$i] = array(
									'grade' => $row->value,
									'l_grade' => $row->letter_grade,
									'rem' => $row->remarks
										);
				
			}
		}
		return $grade_arr;
	}
	
  /* Get ========================================= */
	function get($id){
		$q = $this->db->where('id', $id)->get('student_grades');
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	
  /* Update ========================================= */
	function update($id, $data){
		$this->db->where('id', $id)->update('student_grades', $data);
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	/*
		function update_per_grading_period
		updates student grade per grading period
		param: $grade_details (array)
			-> grade_details contains grade(int), grading period(int), enrollment id(int), subject id(int)
	*/
	function update_per_grading_period($grade_details)
	{
		
		$grade = $grade_details['grade'];
		$period = $grade_details['grading_period'];
		$e_id = $grade_details['enrollment_id'];
		$subj_id = $grade_details['subject_id'];
		$l_grade = $grade_details['letter_grade'];
		//$remark = $grade_details['remark'];
		
		$s_sql = "SELECT * FROM student_grades 
					WHERE grading_period_id = ?
					AND enrollment_id = ?
					AND subject_id = ?";
		$s_query = $this->db->query($s_sql, array($period, $e_id, $subj_id));
		
		if($s_query->num_rows > 0)
		{
			// add remarks
			$sql = "UPDATE student_grades
					SET value = ?, 
						letter_grade = ?
				WHERE grading_period_id = ?
				AND enrollment_id = ?
				AND subject_id = ?";
			$query = $this->db->query($sql, array($grade, $l_grade, $period, $e_id, $subj_id));
		}else
		{
			$i_sql = "INSERT INTO student_grades(subject_id, enrollment_id, grading_period_id, value,letter_grade)
					VALUES(?,?,?,?,?)";
			$i_query = $this->db->query($i_sql, array($subj_id, $e_id, $period, $grade,$l_grade));
		}
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	function insert_grade_per_grading_period($grade_details)
	{
		$grade = $grade_details['grade'];
		$l_grade = $grade_details['letter_grade'];
		$period = $grade_details['grading_period'];
		$e_id = $grade_details['enrollment_id'];
		$subj_id = $grade_details['subject_id'];
		
		
		$i_sql = "INSERT INTO student_grades(subject_id, enrollment_id, grading_period_id, value,letter_grade)
					VALUES(?,?,?,?,?)";
		$i_query = $this->db->query($i_sql, array($subj_id, $e_id, $period, $grade,$l_grade));
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function get_grading_system()
	{
		$q = $this->db->order_by('range_start DESC')->get('grading_system');
		
		if($q->num_rows() >= 1)
		{
			foreach($q->result() as $k => $v)
			{
				$data[$v->varname]['rangefrom'] = $v->range_start;
				$data[$v->varname]['rangeto'] = $v->range_end;
				$data[$v->varname]['id'] = $v->id;
				$data[$v->varname]['letter'] = $v->letter;
				$data[$v->varname]['badge'] = $v->badge;
			}
			

			return $data;
			
		}else{
			return FALSE;
		}
		// return $q->num_rows() >=1 ? $q->result() : FALSE;
	}
	
	public function create_grading_system($input)
	{
		
		$letter = ['A+','A','A-','B+','B','B-','C+','C','C-','D','No Grade'];
		$count = 0;
		foreach($input['data'] as $k => $v)
		{
			$data[$count]['varname'] = $k;
			$data[$count]['letter'] = $letter[$count];
			$data[$count]['range_start'] = $v[$k.'_from'];
			$data[$count]['range_end'] = $v[$k.'_to'];
			$count++;
		}
		
		// pd($data);
		
		$this->db->insert_batch('grading_system',$data);
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}
	
	
	public function update_grading_system($input)
	{
		// pd($input);
		// $count = 0;
		// foreach($input as $k => $v)
		// {
			// $data[$count]['id'] = $k;
			// $data[$count]['graderange']= $v;
			// $count++;
		// }
		
		
		
		foreach($input as $k => $v)
		{
			$root = substr(key($v),0,strpos(key($v),'_'));
			$data[$k]['id'] = $k;
			$data[$k]['range_start'] = $v[$root.'_from'];
			$data[$k]['range_end'] = $v[$root.'_to'];
		}
		
		// pd($data);
		
		$this->db->update_batch('grading_system',$data,'id');
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}
	
	public function generate_remarks($e_id)
	{
		$sql = 'SELECT id 
				FROM student_remarks
				WHERE e_id = ?
				AND ay_id = ?';
		$q= $this->db->query($sql,array($e_id,$this->c_sy->id));
		
		if($q->num_rows() >=1 )
		{
			return TRUE;
		}else{
			$gp = "SELECT gp_id FROM grading_periods";
			$gp_res = $this->db->query($gp);

			if($gp_res->num_rows() > 0)
			{
				foreach($gp_res->result() as $k=> $v)
				{
					$for_remarks[$k]['e_id'] = $e_id;
					$for_remarks[$k]['ay_id'] = $this->c_sy->id;
					$for_remarks[$k]['gp_id'] = $v->gp_id;
					$for_remarks[$k]['created'] = NOW;
				}
				
				$this->db->insert_batch('student_remarks',$for_remarks);
				return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
			}else{
				return FALSE;
			}
			
		}
	}
	
	/**
	 * Generate Student Grade From Curriculum Subjects
	 */
	public function generate_grades($e_id, $level_id)
	{
		$this->load->model('M_enrollees');
			
		if($this->M_enrollees->check_enrollment_id(intval($e_id)))
		{
			$this->load->model('M_student_subjects');
			return $this->M_student_subjects->generate_student_subjects(intval($e_id),intval($level_id));
		}
		return false;
	}
}
?>
